/*
 * Created on 2005/4/12
 *
 */
package com.erp.app.report.genericInquiry.inquiryMaster.ejb;

import java.security.InvalidParameterException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Hashtable;
import java.util.Iterator;

import com.erp.app.common.ProjectConstants;
import com.erp.app.report.genericInquiry.inquiryCriteria.dao.InquiryCriteriaDAO;
import com.erp.app.report.genericInquiry.inquiryDisplay.dao.InquiryDisplayDAO;
import com.erp.app.report.genericInquiry.inquiryMaster.beans.GenericInquiryMasterVO;
import com.erp.app.report.genericInquiry.inquiryMaster.dao.GenericInquirySearchCriteria;
import com.erp.app.report.genericInquiry.inquiryOutput.beans.vo.InquiryOutputVO;
import com.erp.app.report.genericInquiry.inquirySp.dao.InquirySpDAO;
import com.erp.framework.exceptions.FindRecordException;
import com.erp.framework.model.dao.ServiceLocator;
import com.erp.framework.model.ejb.SimpleSearchMaintStyleEjb;
import com.erp.framework.model.vo.ChildList;
import com.erp.framework.util.CommonUtil;
import com.erp.framework.util.logger.Logger;

public class GenericInquiryMasterManagerEJB extends SimpleSearchMaintStyleEjb {
    public GenericInquiryMasterVO initialize(String inquiryType, String loginUserCode, String lang) throws FindRecordException {
        GenericInquiryMasterVO vo = new GenericInquiryMasterVO(inquiryType);
        vo.setLang(lang);

        // retrieve the searching criteria first, then the store proc info
        InquiryCriteriaDAO criteriaDAO = new InquiryCriteriaDAO();
        InquirySpDAO spDAO = new InquirySpDAO();

        Connection conn = null;
        //ConnectionPool pool = null;
        ServiceLocator srvLocator = ServiceLocator.getInstance();
        //pool = srvLocator.getConnectionPool(loginUserCode);

        try {
        	//conn = pool.getConnection();
        	conn = srvLocator.getDSDBConnection();
        	
            ChildList criteriaList = criteriaDAO.selectByParent(vo, conn);
            ChildList spList = spDAO.selectByParent(vo, conn);

            vo.storeChilds(criteriaList);
            vo.storeChilds(spList);
        } catch (Exception e) {
            throw new FindRecordException(e.getMessage());
        } finally {
        	//pool.releaseConnection(conn);
        	if (conn != null) {
        		try {	
        			conn.close();
        		} catch (Exception e) {
        			Logger.logExceptions(this, e);
        		}
        	}
        }

        return vo;
    }

    public Collection getDefaultCriteriaValues(Collection queries, String loginUserCode) throws FindRecordException, SQLException {
        if (queries == null)
            throw new InvalidParameterException("queries cannot be null");

        if (queries.size() != 2)
            throw new InvalidParameterException("queries collection should have two collections embedded in it.");

        ArrayList masterList = new ArrayList();
        ArrayList fromList = new ArrayList();
        ArrayList toList = new ArrayList();

        masterList.add(fromList);
        masterList.add(toList);

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        Iterator it = queries.iterator();
        Iterator fQueriesIt = ((Collection)it.next()).iterator();
        Iterator tQueriesIt = ((Collection)it.next()).iterator();
        
        //ConnectionPool pool = null;
        ServiceLocator srvLocator = ServiceLocator.getInstance();
        //pool = srvLocator.getConnectionPool(loginUserCode);

        String query = "";

        try {
            //conn = pool.getConnection();
        	conn = srvLocator.getDSDBConnection();
            stmt = conn.createStatement();

            while (fQueriesIt.hasNext()) {
                query = ((String)fQueriesIt.next()).trim();

                if (CommonUtil.isEmpty(query)) {
                    fromList.add("");
                    continue;
                }

                rs = stmt.executeQuery(query);
                rs.next();
                fromList.add(rs.getString(1));
            }

            while (tQueriesIt.hasNext()) {
                query = ((String)tQueriesIt.next()).trim();

                if (CommonUtil.isEmpty(query)) {
                    toList.add("");
                    continue;
                }

                rs = stmt.executeQuery(query);
                rs.next();
                toList.add(rs.getString(1));
            }

        } catch (Exception e) {
            throw new FindRecordException(e.getMessage());
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
            	//pool.releaseConnection(conn);
            	try {
            		conn.close();
            	} catch (Exception e) {
            		Logger.logExceptions(this, e);
            	}
            }

        }

        return masterList;
    }

    public GenericInquiryMasterVO generate(GenericInquirySearchCriteria criteria, String loginUserCode) throws FindRecordException {
        Connection conn = null;
        //ConnectionPool pool = null;
        ServiceLocator srvLocator = ServiceLocator.getInstance();
        //pool = srvLocator.getConnectionPool(loginUserCode);
        GenericInquiryMasterVO rslt = new GenericInquiryMasterVO(criteria.getSrchInquiryType());
        rslt.setCompanyCode(criteria.getSrchCompanyCode());

        try {
            InquirySpDAO spDAO = new InquirySpDAO();
            InquiryDisplayDAO displayDAO = new InquiryDisplayDAO();

            //conn = pool.getConnection();
            conn = srvLocator.getDSDBConnection();

            // attempt to generate the table where the report will be held
            Hashtable params = spDAO.createTable(criteria, conn);
            String reportTime = (String) params.get(ProjectConstants.GENERIC_INQUIRY_PARAM_RPT_TIME);
            String filename = (String)params.get(ProjectConstants.GENERIC_INQUIRY_PARAM_FILENAME);

            // then we attempt to retrieve the table headers
            rslt.setRptTime(reportTime);
            rslt.setFilename(filename);
            ChildList displayList = displayDAO.selectByParent(rslt, conn);

            if (criteria.isUseRefCursor()) {
	            ArrayList outputList = (ArrayList) params.get(ProjectConstants.GENERIC_INQUIRY_PARAM_RESULT_LIST);
	            ChildList outputCList = new ChildList(InquiryOutputVO.class);
	            outputCList.addAll(outputList);
	            rslt.storeChilds(outputCList);
            }

            rslt.storeChilds(displayList);
        } catch (Exception e) {
            Logger.logExceptions(this, e);
            throw new FindRecordException(e.getMessage());
        } finally {
        	//pool.releaseConnection(conn);
        	if (conn != null) {
        		try {
        			conn.close();
        		} catch (Exception e) {
        			Logger.logExceptions(this, e);
        		}
        	}
        }
        //Logger.cruise(this, "Generate, exit.");

        return rslt;
    }
}
